/******************************************************************************
Description:
This file converts all the txt files to stata format needed to analyze
NYC middle schools. It also standardizes the
Student ID to omit any leading zeros: e.g "012345" -> "12345"
********************************************************************************/

clear all

* Switches
// Match Files
	local priority		1
	local ms_match		1
	local talent		1

// Demographics / Enrollment
	local demographics 	1
	local enrollment	1

// Outcomes
	local ny_test_g38	1

/*****************************************************************************

   Match Files

 ****************************************************************************/

// MS Matches

if `ms_match' == 1 {

	forval year = 2016/2018 {

		* load
		if `year'==2016{
			import delimited using "${rawdata}NYCDOE/MiddleSchoolAdmissions/Received 10.18.19//`year'_MS_Admissions_for_MIT_20191009.csv", delim (",") varnames(1) clear
		}
		if inlist(`year',2017,2018){
			import delimited using "${rawdata}NYCDOE/MiddleSchoolAdmissions/Received 10.18.19//`year'_MS_Admissions_for_MIT_20191015.csv", delim (",") varnames(1) clear
		}

		* rename variables
		if `year'==2016{
			cap rename	student_id_scram	student_id_scram
			cap rename	finaloffer	finaloffer
			cap rename	finalofferdbn	finalofferdbn
			cap rename	mrmatch	mrmatch
			cap rename	mrmatchdbn	mrmatchdbn
			cap rename	mrmp	mrmp
			cap rename	mrmpdbn	mrmpdbn
			cap rename	nsmatch	nsmatch
			cap rename	nsmatchdbn	nsmatchdbn
			cap rename	nsmp	nsmp
			cap rename	nsmpdbn	nsmpdbn
			cap rename	nsofferresponse	nsofferresponse
			cap rename	appealmatch	appealmatch
			cap rename	appealmatchdbn	appealmatchdbn
			cap rename	appealmp	appealmp
			cap rename	appealmpdbn	appealmpdbn
			cap rename	eszoneddbn	eszoneddbn
			cap rename	zonedschooldbn	zonedschooldbn
			cap rename	currentgrade	currentgrade
			cap rename	sped	sped
			cap rename	currentschooldbn	currentschooldbn
			cap rename	elaproficiencyrating	elaproficiencyrating
			cap rename	mathproficiencyrating	mathproficiencyrating
			cap rename	daysabsent	daysabsent
			cap rename	dayslate	dayslate
			cap rename	ss	ss
			cap rename	sc	sc
			cap rename	timemanagementindependence	timemanagementindependence
			cap rename	organization	organization
			cap rename	perseverance	perseverance
			cap rename	asksforhelp	asksforhelp
			cap rename	respectsschoolrulescollaboration	respectsschoolrulescollaboration
			cap rename	talent1	talent1
			cap rename	talent1score	talent1score
			cap rename	talent2	talent2
			cap rename	talent2score	talent2score
			cap rename	roundmatched	roundmatched
			cap rename	tiebreaker	tiebreaker
			cap rename	programname1	programname1
			cap rename	programcode1	programcode1
			cap rename	programtype1	programtype1
			cap rename	schooldbn1	schooldbn1
			cap rename	programpriority1	choice1
			cap rename	pclrank1	rank1
			cap rename	compositescore1	compositescore1
			cap rename	matchprioritygroup1	matchprioritygroup1
			cap rename	programname2	programname2
			cap rename	programcode2	programcode2
			cap rename	programtype2	programtype2
			cap rename	schooldbn2	schooldbn2
			cap rename	programpriority2	choice2
			cap rename	pclrank2	rank2
			cap rename	compositescore2	compositescore2
			cap rename	matchprioritygroup2	matchprioritygroup2
			cap rename	programname3	programname3
			cap rename	programcode3	programcode3
			cap rename	programtype3	programtype3
			cap rename	schooldbn3	schooldbn3
			cap rename	programpriority3	choice3
			cap rename	pclrank3	rank3
			cap rename	compositescore3	compositescore3
			cap rename	matchprioritygroup3	matchprioritygroup3
			cap rename	programname4	programname4
			cap rename	programcode4	programcode4
			cap rename	programtype4	programtype4
			cap rename	schooldbn4	schooldbn4
			cap rename	programpriority4	choice4
			cap rename	pclrank4	rank4
			cap rename	compositescore4	compositescore4
			cap rename	matchprioritygroup4	matchprioritygroup4
			cap rename	programname5	programname5
			cap rename	programcode5	programcode5
			cap rename	programtype5	programtype5
			cap rename	schooldbn5	schooldbn5
			cap rename	programpriority5	choice5
			cap rename	pclrank5	rank5
			cap rename	compositescore5	compositescore5
			cap rename	matchprioritygroup5	matchprioritygroup5
			cap rename	programname6	programname6
			cap rename	programcode6	programcode6
			cap rename	programtype6	programtype6
			cap rename	schooldbn6	schooldbn6
			cap rename	programpriority6	choice6
			cap rename	pclrank6	rank6
			cap rename	compositescore6	compositescore6
			cap rename	matchprioritygroup6	matchprioritygroup6
			cap rename	programname7	programname7
			cap rename	programcode7	programcode7
			cap rename	programtype7	programtype7
			cap rename	schooldbn7	schooldbn7
			cap rename	programpriority7	choice7
			cap rename	pclrank7	rank7
			cap rename	compositescore7	compositescore7
			cap rename	matchprioritygroup7	matchprioritygroup7
			cap rename	programname8	programname8
			cap rename	programcode8	programcode8
			cap rename	programtype8	programtype8
			cap rename	schooldbn8	schooldbn8
			cap rename	programpriority8	choice8
			cap rename	pclrank8	rank8
			cap rename	compositescore8	compositescore8
			cap rename	matchprioritygroup8	matchprioritygroup8
			cap rename	programname9	programname9
			cap rename	programcode9	programcode9
			cap rename	programtype9	programtype9
			cap rename	schooldbn9	schooldbn9
			cap rename	programpriority9	choice9
			cap rename	pclrank9	rank9
			cap rename	compositescore9	compositescore9
			cap rename	matchprioritygroup9	matchprioritygroup9
			cap rename	programname10	programname10
			cap rename	programcode10	programcode10
			cap rename	programtype10	programtype10
			cap rename	schooldbn10	schooldbn10
			cap rename	programpriority10	choice10
			cap rename	pclrank10	rank10
			cap rename	compositescore10	compositescore10
			cap rename	matchprioritygroup10	matchprioritygroup10
			cap rename	programname11	programname11
			cap rename	programcode11	programcode11
			cap rename	programtype11	programtype11
			cap rename	schooldbn11	schooldbn11
			cap rename	programpriority11	choice11
			cap rename	pclrank11	rank11
			cap rename	compositescore11	compositescore11
			cap rename	matchprioritygroup11	matchprioritygroup11
			cap rename	programname12	programname12
			cap rename	programcode12	programcode12
			cap rename	programtype12	programtype12
			cap rename	schooldbn12	schooldbn12
			cap rename	programpriority12	choice12
			cap rename	pclrank12	rank12
			cap rename	compositescore12	compositescore12
			cap rename	matchprioritygroup12	matchprioritygroup12
			cap rename	nsprogrampriority1	nsprogrampriority1
			cap rename	nsprogramcode1	nsprogramcode1
			cap rename	nsprogramname1	nsprogramname1
			cap rename	nsschooldbn1	nsschooldbn1
			cap rename	nsprogramtype1	nsprogramtype1
			cap rename	nspclrank1	nspclrank1
			cap rename	nsprogrampriority2	nsprogrampriority2
			cap rename	nsprogramcode2	nsprogramcode2
			cap rename	nsprogramname2	nsprogramname2
			cap rename	nsschooldbn2	nsschooldbn2
			cap rename	nsprogramtype2	nsprogramtype2
			cap rename	nspclrank2	nspclrank2
			cap rename	nsprogrampriority3	nsprogrampriority3
			cap rename	nsprogramcode3	nsprogramcode3
			cap rename	nsprogramname3	nsprogramname3
			cap rename	nsschooldbn3	nsschooldbn3
			cap rename	nsprogramtype3	nsprogramtype3
			cap rename	nspclrank3	nspclrank3
			cap rename	nsprogrampriority4	nsprogrampriority4
			cap rename	nsprogramcode4	nsprogramcode4
			cap rename	nsprogramname4	nsprogramname4
			cap rename	nsschooldbn4	nsschooldbn4
			cap rename	nsprogramtype4	nsprogramtype4
			cap rename	nspclrank4	nspclrank4
			cap rename	appealtiebreaker	appealtiebreaker
			cap rename	appealprogrampriority1	appealprogrampriority1
			cap rename	appealprogramcode1	appealprogramcode1
			cap rename	appealprogramname1	appealprogramname1
			cap rename	appealschooldbn1	appealschooldbn1
			cap rename	appealprogramtype1	appealprogramtype1
			cap rename	appealpclrank1	appealpclrank1
			cap rename	appealcompositescore1	appealcompositescore1
			cap rename	appealprogrampriority2	appealprogrampriority2
			cap rename	appealprogramcode2	appealprogramcode2
			cap rename	appealprogramname2	appealprogramname2
			cap rename	appealschooldbn2	appealschooldbn2
			cap rename	appealprogramtype2	appealprogramtype2
			cap rename	appealpclrank2	appealpclrank2
			cap rename	appealcompositescore2	appealcompositescore2
			cap rename	appealprogrampriority3	appealprogrampriority3
			cap rename	appealprogramcode3	appealprogramcode3
			cap rename	appealprogramname3	appealprogramname3
			cap rename	appealschooldbn3	appealschooldbn3
			cap rename	appealprogramtype3	appealprogramtype3
			cap rename	appealpclrank3	appealpclrank3
			cap rename	appealcompositescore3	appealcompositescore3
			cap rename	appealprogrampriority4	appealprogrampriority4
			cap rename	appealprogramcode4	appealprogramcode4
			cap rename	appealprogramname4	appealprogramname4
			cap rename	appealschooldbn4	appealschooldbn4
			cap rename	appealprogramtype4	appealprogramtype4
			cap rename	appealpclrank4	appealpclrank4
			cap rename	appealcompositescore4	appealcompositescore4
			cap rename	appealprogrampriority5	appealprogrampriority5
			cap rename	appealprogramcode5	appealprogramcode5
			cap rename	appealprogramname5	appealprogramname5
			cap rename	appealschooldbn5	appealschooldbn5
			cap rename	appealprogramtype5	appealprogramtype5
			cap rename	appealpclrank5	appealpclrank5
			cap rename	appealcompositescore5	appealcompositescore5
			cap rename	appealprogrampriority6	appealprogrampriority6
			cap rename	appealprogramcode6	appealprogramcode6
			cap rename	appealprogramname6	appealprogramname6
			cap rename	appealschooldbn6	appealschooldbn6
			cap rename	appealprogramtype6	appealprogramtype6
			cap rename	appealprogrampriority7	appealprogrampriority7
			cap rename	appealprogramcode7	appealprogramcode7
			cap rename	appealprogramname7	appealprogramname7
			cap rename	appealschooldbn7	appealschooldbn7
			cap rename	appealprogramtype7	appealprogramtype7
		}
		if `year'==2017{
			cap rename	student_id_scram	student_id_scram
			cap rename	finaloffer	finaloffer
			cap rename	finalofferdbn	finalofferdbn
			cap rename	mrmatch	mrmatch
			cap rename	mrmatchdbn	mrmatchdbn
			cap rename	mrmp	mrmp
			cap rename	mrmpdbn	mrmpdbn
			cap rename	nsmatch	nsmatch
			cap rename	nsmatchdbn	nsmatchdbn
			cap rename	nsmp	nsmp
			cap rename	nsmpdbn	nsmpdbn
			cap rename	nsofferresponse	nsofferresponse
			cap rename	appealmatch	appealmatch
			cap rename	appealmatchdbn	appealmatchdbn
			cap rename	appealmp	appealmp
			cap rename	appealmpdbn	appealmpdbn
			cap rename	eszoneddbn	eszoneddbn
			cap rename	zonedschooldbn	zonedschooldbn
			cap rename	currentgrade	currentgrade
			cap rename	sped	sped
			cap rename	currentschooldbn	currentschooldbn
			cap rename	elaproficiencyrating	elaproficiencyrating
			cap rename	mathproficiencyrating	mathproficiencyrating
			cap rename	accessiblesiterequired	accessiblesiterequired
			cap rename	daysabsent	daysabsent
			cap rename	dayslate	dayslate
			cap rename	math	math
			cap rename	ela	ela
			cap rename	ss	ss
			cap rename	sc	sc
			cap rename	timemanagementindependence	timemanagementindependence
			cap rename	organization	organization
			cap rename	perseverance	perseverance
			cap rename	asksforhelp	asksforhelp
			cap rename	respectsschoolrulescollaboration	respectsschoolrulescollaboration
			cap rename	talent1	talent1
			cap rename	talent1score	talent1score
			cap rename	talent2	talent2
			cap rename	talent2score	talent2score
			cap rename	roundmatched	roundmatched
			cap rename	tiebreaker	tiebreaker
			cap rename	programname1	programname1
			cap rename	programcode1	programcode1
			cap rename	programtype1	programtype1
			cap rename	schooldbn1	schooldbn1
			cap rename	pclrank1	rank1
			cap rename	compositescore1	compositescore1
			cap rename	matchprioritygroup1	matchprioritygroup1
			cap rename	programname2	programname2
			cap rename	schooldbn2	programtype2
			cap rename	programtype2	schooldbn2
			cap rename	pclrank2	rank2
			cap rename	compositescore2	compositescore2
			cap rename	matchprioritygroup2	matchprioritygroup2
			cap rename	programname3	programname3
			cap rename	programcode3	programcode3
			cap rename	programtype3	programtype3
			cap rename	schooldbn3	schooldbn3
			cap rename	pclrank3	rank3
			cap rename	compositescore3	compositescore3
			cap rename	matchprioritygroup3	matchprioritygroup3
			cap rename	programname4	programname4
			cap rename	programcode4	programcode4
			cap rename	programtype4	programtype4
			cap rename	schooldbn4	schooldbn4
			cap rename	pclrank4	rank4
			cap rename	compositescore4	compositescore4
			cap rename	matchprioritygroup4	matchprioritygroup4
			cap rename	programname5	programname5
			cap rename	programcode5	programcode5
			cap rename	programtype5	programtype5
			cap rename	schooldbn5	schooldbn5
			cap rename	pclrank5	rank5
			cap rename	compositescore5	compositescore5
			cap rename	matchprioritygroup5	matchprioritygroup5
			cap rename	programname6	programname6
			cap rename	programcode6	programcode6
			cap rename	programtype6	programtype6
			cap rename	schooldbn6	schooldbn6
			cap rename	pclrank6	rank6
			cap rename	compositescore6	compositescore6
			cap rename	matchprioritygroup6	matchprioritygroup6
			cap rename	programname7	programname7
			cap rename	programcode7	programcode7
			cap rename	programtype7	programtype7
			cap rename	schooldbn7	schooldbn7
			cap rename	pclrank7	rank7
			cap rename	compositescore7	compositescore7
			cap rename	matchprioritygroup7	matchprioritygroup7
			cap rename	programname8	programname8
			cap rename	programcode8	programcode8
			cap rename	programtype8	programtype8
			cap rename	schooldbn8	schooldbn8
			cap rename	pclrank8	rank8
			cap rename	compositescore8	compositescore8
			cap rename	matchprioritygroup8	matchprioritygroup8
			cap rename	programname9	programname9
			cap rename	programcode9	programcode9
			cap rename	programtype9	programtype9
			cap rename	schooldbn9	schooldbn9
			cap rename	pclrank9	rank9
			cap rename	compositescore9	compositescore9
			cap rename	matchprioritygroup9	matchprioritygroup9
			cap rename	programname10	programname10
			cap rename	programcode10	programcode10
			cap rename	programtype10	programtype10
			cap rename	schooldbn10	schooldbn10
			cap rename	pclrank10	rank10
			cap rename	compositescore10	compositescore10
			cap rename	matchprioritygroup10	matchprioritygroup10
			cap rename	programname11	programname11
			cap rename	programcode11	programcode11
			cap rename	programtype11	programtype11
			cap rename	schooldbn11	schooldbn11
			cap rename	pclrank11	rank11
			cap rename	compositescore11	compositescore11
			cap rename	matchprioritygroup11	matchprioritygroup11
			cap rename	programname12	programname12
			cap rename	programcode12	programcode12
			cap rename	programtype12	programtype12
			cap rename	schooldbn12	schooldbn12
			cap rename	pclrank12	rank12
			cap rename	compositescore12	compositescore12
			cap rename	matchprioritygroup12	matchprioritygroup12
			cap rename	nsprogrampriority1	nsprogrampriority1
			cap rename	nsprogramcode1	nsprogramcode1
			cap rename	nsprogramname1	nsprogramname1
			cap rename	nsschooldbn1	nsschooldbn1
			cap rename	nsprogramtype1	nsprogramtype1
			cap rename	nspclrank1	nspclrank1
			cap rename	nsprogrampriority2	nsprogrampriority2
			cap rename	nsprogramcode2	nsprogramcode2
			cap rename	nsprogramname2	nsprogramname2
			cap rename	nsschooldbn2	nsschooldbn2
			cap rename	nsprogramtype2	nsprogramtype2
			cap rename	nspclrank2	nspclrank2
			cap rename	nsprogrampriority3	nsprogrampriority3
			cap rename	nsprogramcode3	nsprogramcode3
			cap rename	nsprogramname3	nsprogramname3
			cap rename	nsschooldbn3	nsschooldbn3
			cap rename	nsprogramtype3	nsprogramtype3
			cap rename	nspclrank3	nspclrank3
			cap rename	nsprogrampriority4	nsprogrampriority4
			cap rename	nsprogramcode4	nsprogramcode4
			cap rename	nsprogramname4	nsprogramname4
			cap rename	nsschooldbn4	nsschooldbn4
			cap rename	nsprogramtype4	nsprogramtype4
			cap rename	nspclrank4	nspclrank4
			cap rename	appealtiebreaker	appealtiebreaker
			cap rename	appealprogrampriority1	appealprogrampriority1
			cap rename	appealprogramcode1	appealprogramcode1
			cap rename	appealprogramname1	appealprogramname1
			cap rename	appealschooldbn1	appealschooldbn1
			cap rename	appealprogramtype1	appealprogramtype1
			cap rename	appealpclrank1	appealpclrank1
			cap rename	appealcompositescore1	appealcompositescore1
			cap rename	appealprogrampriority2	appealprogrampriority2
			cap rename	appealprogramcode2	appealprogramcode2
			cap rename	appealprogramname2	appealprogramname2
			cap rename	appealschooldbn2	appealschooldbn2
			cap rename	appealprogramtype2	appealprogramtype2
			cap rename	appealpclrank2	appealpclrank2
			cap rename	appealcompositescore2	appealcompositescore2
			cap rename	appealprogrampriority3	appealprogrampriority3
			cap rename	appealprogramcode3	appealprogramcode3
			cap rename	appealprogramname3	appealprogramname3
			cap rename	appealschooldbn3	appealschooldbn3
			cap rename	appealprogramtype3	appealprogramtype3
			cap rename	appealpclrank3	appealpclrank3
			cap rename	appealcompositescore3	appealcompositescore3
			cap rename	appealprogrampriority4	appealprogrampriority4
			cap rename	appealprogramcode4	appealprogramcode4
			cap rename	appealprogramname4	appealprogramname4
			cap rename	appealschooldbn4	appealschooldbn4
			cap rename	appealprogramtype4	appealprogramtype4
			cap rename	appealpclrank4	appealpclrank4
			cap rename	appealcompositescore4	appealcompositescore4
			cap rename	appealprogrampriority5	appealprogrampriority5
			cap rename	appealprogramcode5	appealprogramcode5
			cap rename	appealprogramname5	appealprogramname5
			cap rename	appealschooldbn5	appealschooldbn5
			cap rename	appealprogramtype5	appealprogramtype5
			cap rename	appealpclrank5	appealpclrank5
			cap rename	appealcompositescore5	appealcompositescore5
			cap rename	appealprogrampriority6	appealprogrampriority6
			cap rename	appealprogramcode6	appealprogramcode6
			cap rename	appealprogramname6	appealprogramname6
			cap rename	appealschooldbn6	appealschooldbn6
			cap rename	appealprogramtype6	appealprogramtype6
			cap rename	appealprogrampriority7	appealprogrampriority7
			cap rename	appealprogramcode7	appealprogramcode7
			cap rename	appealprogramname7	appealprogramname7
			cap rename	appealschooldbn7	appealschooldbn7
			cap rename	appealprogramtype7	appealprogramtype7
			cap rename	appealprogrampriority8	appealprogrampriority8
			cap rename	appealprogramcode8	appealprogramcode8
			cap rename	appealprogramname8	appealprogramname8
			cap rename	appealschooldbn8	appealschooldbn8
			cap rename	appealprogramtype8	appealprogramtype8
			cap rename	optout				optout
		}

		* save
		qui compress
		save "${cleandata}match/MiddleSchoolAdmissions/`year'_MS_Admissions_scrambled.dta", replace

	}
}

// Priorities

if `priority' == 1 {
	import delimited using "${rawdata}NYCDOE/MiddleSchoolAdmissions/Received 5.13.20/iteration_69_student_seat_groups.csv", clear
	rename program_code programcode
	keep if grade ==6
	drop grade

	reshape wide priority, i(student_id_scram programcode) j(seat_number)

	rename student_id_scram stu
	tostring stu, replace
	save "${cleandata}program_priorities_2019.dta", replace
}

// Talen-Program Crosswalk

if `talent' == 1 {
	import excel using  "${rawdata}NYCDOE/MiddleSchoolAdmissions/Received 10.18.19//Talent_Program_Crosswalk.xlsx", first clear
	save "${cleandata}talent_program_crosswalk.dta", replace
}


/*****************************************************************************

   Demographics / Enrollment

 ****************************************************************************/

// Demographics
if `demographics' == 1 {

	forval year = 2005/2018 {

		local digits_plus = `year' - 2000 + 1
		if length("`digits_plus'") == 1 local digits_plus 0`digits_plus'
		import delimited using "${rawdata}NYCDOE/JuneBiog/`year'-`digits_plus'_June-Biog_PK-12_Scrambled.csv",  clear  delimiters(",") stringcols(_all) // sheet("Summary")

		eliminate_leading_0
		compress
		save "${cleandata}demos/JuneBiog/`year'-`digits_plus'_June-Biog_PK-12_Scrambled.dta", replace

	}
}

/*****************************************************************************

  OUTCOMES

 ****************************************************************************/

// New York Grade 3-8 Assessments
if `ny_test_g38' == 1 {
	forval year = 2005/2018 {

		local digits_plus = `year' - 2000 + 1
		if length("`digits_plus'") == 1 local digits_plus 0`digits_plus'
		import delimited using "${rawdata}NYCDOE/TestBiog/`year'-`digits_plus'_Student_test-Biog_All_G38_NYC_Scrambled.csv",  clear  delimiters(",") stringcols(_all) // sheet("Summary")
		eliminate_leading_0
		compress
		save "${cleandata}outcomes/TestBiog/`year'-`digits_plus'_Student_test-Biog_All_G38_NYC_Scrambled.dta", replace

	}
}
